"""
mysql  从 1 开始
python 从 0 开始

TODO 聚合函数

  把所有学生的名字合并成一行：
    select group_concat(name) from student;
    select group_concat(name separator '。。。') from student;

  分组、按照英语成绩降序排序：
    select gender,group_concat(name order by english desc separator '<>') from student group by gender;

TODO 数学函数

 abs(x)            x的绝对值

 ceil(x)           >x 或 =x 的最小整数（向上取整）
 floor(x)          <x 或 =x 的最大整数（向下取整）

 greatest(x,y,z)   列表中最大值
 least(x,y,x)      列表中最小值

 max(表达式)        一列的最大值
 min(表达式)        一列的最小值

 mod(x,y)          x/y后取余数

 pi()              圆周率

 pow/power(x,y)    x的y次方 - x^y

 rand()            0-1的随机数
 rand()*100        0-100的随机数

 round(x)          四舍五入取整
 round(x,y)        x四舍五入取整，保留y位小数  select name,round(gdp/population/1000,0)*1000 from world where gdp>1000000000000

 truncate(x,y)     x直接截取y位小数，不做任何操作

TODO 字符串函数

char_length(s)                字符串 s 的字符长度
character_length(s)           字符串 s 的字符长度

length(s)                     字符串 s 的字节长度

concat(x,y)                   合并为一个字符串           select name from world where capital like  concat(name, ' City')
                                                      select capital,name from world where capital like concat(name,'%')

concat_ws(<,x,y)              合并x、y为一个字符串，用间隔符 <

field(s,s1,s2,s3)             字符串 s 在字符串列表 (s1,s2,s3) 中第一次出现的位置

ltrim(s)                      去掉开始的空格
rtrim(s)                      去掉结尾的空格
trim(s)                       去掉首尾的空格

mid(s,2,3)                    从s中的第2个位置开始，截取长度为3的子字符串

    select name,  mid( capital, char_length(name)+1, char_length(capital) - char_length(name) ) ext
    from world  where capital like concat(name,'%') and char_length(capital)>char_length(name)

substr/substring(s,n,len)     同mid

position(s1 in s)             从s中获取s1的开始位置

replace(s,s1,s2)              s中，用s2替代s1

reverse(s)                    将s反转

right(s,3)                    s的后3个字符串

strcmp(s1,s2)                比较s1和s2，s1 = s2，返回 0
                                       s1 > s2，返回 1
                                       s1 < s2，返回 -1

ucase(s)                     转换为大写
upper(s)

lcase(s)                     转换为小写
lower(s)


TODO 日期函数

unix_timestamp()                                         从1970-01-01起，到当前的 毫秒值
unix_timestamp('2024-5-25 08:08:08')                     从1970-01-01起，到2024-5-25 08：08：08的毫秒值  - 返回1716595688
from_unixtime(1716595688,'%Y-%m_%d %H:%i:%s')            返回 2024-5-25 08：08：08

curdate/current_date()                                    当前 年月日
curtime/current_time()                                    当前 时分秒
current_timestamp/now()                                       当前 年月日 + 时分秒

date()                                                    从标准的日期字符串获取 年月日
datediff(d1,d2)                                            d1和d2日期之间 差多少天

timediff(t1,t2)                                            t1和t2时间之前差多少，秒级

date_format('2021-1-1 1:1:1','%Y-%m_%d %H:%i:%s')           日期格式化：    返回 2021-01-01 01:01:01
str_to_date('2021-1-1 1:1:1','%Y-%m_%d %H:%i:%s')           字符串转为日期： 同上

date_sub('2021-10-01',interval 2 day)                       减2天（周、月等）
adddate/date_add('2021-10-01',interval 2 day)               加2天

extract(hour from '2021-10-01')                             获取小时（年、月等）

last_day('2021-10-01')                                      所在月的最后一天                      （返回：2020-02-29）

makedate('2021',53)                                         2021年的第53天                      （返回：2021-02-22）

year('2024-5-25 08:08:08')    年
month()                       月
day()                         日
hour()                        时
minute()                      分
second()                      秒
quarter()                     第几季度，返回 1-4

monthname('2024-5-25 08:08:08')           月份名称  (November)
month()                                   月份值1-12
dayname()                                 星期几名称  (Monday)
dayofmonth()                              本月的第几天
dayofweek()                               本月的第几周 (1星期日，2星期一...)
dayofyear()                               本年的第几天

week/weekofyear()                                   本年的第几周（0-53）
weekday()                                 星期几(0星期一，1星期二)
yearweek('2021-10-01')                    返回202139，2021年的第39周

TODO 控制流函数

1、if

   if(score >= 85, '优秀', '及格')          如果 score >= 85，返回 优秀
   ifnull(a,b)                            如果 a ！= null，返回 a
   coalesce(a,b,c,0)    按顺序检查每个表达式，一旦找到非空值，就返回，如果所有表达式都是null，最终结果也是null
   coalesce(a,0)        和ifnull(a,0）用法一致，如果a ！=null，返回a，如果为null，返回0

   isnull(null)                           如果是null返回0，否则返回1
   nullif(13,12)                          一样，返回null，不一样返回13

2、case when

   select *,
     case payType
        when 1 then '微信支付'
        when 2 then '支付宝支付'
        when 3 then '银行卡支付'
     else
     '其他方式支付'
     end as pay_str
   from oders;

   或：
   select *,
      case
        when payType = 1 then '微信支付'
        when payType = 2 then '支付宝支付'
        when payType = 3 then '银行卡支付'
      else
        '其他方式支付'
      end as pay_str
   from oders;


   select name,
       case when continent='Australasia' then 'Oceania'
       else
            continent
       end
   from world where name like 'N%'

大洋洲成为澳大拉西亚
欧亚大陆和土耳其的国家前往欧洲/亚洲
以"B"开头的加勒比岛屿前往北美，其他加勒比岛屿前往南美洲
显示所有国家/地区的名称、原始大陆和新大陆。

select name,continent as original_continent,
case when continent = 'Oceania' then 'Australasia'
     when continent in ('Eurasia','Turkey') then 'Europe/Asia'
     when continent = 'Caribbean' then
         case when name like 'B%' then 'North America'
         else
             'South America'
         end
else
   continent
end as new_continent
from world;

TODO 窗口函数
1、序号函数
   row_number()      1、2、3、4、5、6
   rank()            1、1、3、4、4、6
   dense_rank()      1、1、2、3、3、4

  对每个部门的员工按照薪资排序，并给出排名：
       select
          ename,
          sal,
          deptno,
          row_number() over(partition by deptno order by sal desc) as rn
       from emp;

  求出每个部门薪资排在前三名的员工：
       select * from (
           select
              ename,
              deptno,
              sal,
              row_number() over(partition by deptno order by sal desc) as rn
              from emp
       ) t  where t.rn <=3;

  对每个员工进行全局排序（不分组）：
       select
          ename,
          sal,
          deptno,
          row_number() over(order by sal desc) as rn
       from emp;


2、开窗聚合函数：
     sum
     avg
     max
     min

  对每个部门的员工薪资按照入职时间排序（每个部门的薪资累加）：
       select
          ename,
          deptno,
          hiredate,
          sal,
          sum(sal) over(partition by deptno order by hiredate) as s

          或：同上
            sum(sal) over(partition by deptno order by hiredate rows between unbounded preceding and current row) as s

          或：本行前三行的和 + 本行
            sum(sal) over(partition by deptno order by hiredate rows between 3 preceding and current row) as s

          或：本行前三行的和 + 本行 + 后一行
            sum(sal) over(partition by deptno order by hiredate rows between 3 preceding and 1 following) as s

          或：本行 + 后面所有行
            sum(sal) over(partition by deptno order by hiredate rows between current row and unbounded following) as s

       from emp;

  对每个部门的员工薪资按照入职时间排序（每个部门的薪资总计）：
       select
          ename,
          deptno,
          hiredate,
          sal,
          sum(sal) over(partition by deptno) as s
       from emp;

3、分布函数
   cume_dist       （查询<=当前薪资的比例）
   percent_rank   （不常用）

   对每个部门员工薪资情况进行分析：
       select
           ename,
           sal,
           deptno,

           分组：<= 本人薪资的有占多少比例
           cume_dist() over(partition by deptno order by sal) as cu1,

           不分组：所有数据为1组
           cume_dist() over(order by sal) as cu2
       from emp;


   对员工薪资进行排序分析：

   r1：1、1、3、4、4、6
   r2：r1/(所在组的行数 - 1)

      select
         ename,
         sal,
         deptno,
         rank() over(partition by deptno order by sal desc) as r1,
         percent_rank() over(partition by deptno order by sal desc) as r2
      from emp;

4、前后函数：
    lag： 本行为准，本行-1的值填充至本行
    lead：本行为准，本行+1的值填充至本行
  应用场景：查询当前同学和前一名成绩的差值

    查询前1名同学的成绩和当前同学成绩的差值：
     time1：分组后，第一行为默认值'2020-01-01'，其余行填充前一行的hiredate
     time2：分组后，前两行没有值，为null，其余行填充前一行的hiredate

     time3：分组后，把第二行的填充到第一行，依次填充，最后一行为默认值'2020-01-01'
     time4：分组后，把第二、三行填充到第一、二行，依次填充，最后两行没有值，为null

      select
         ename,
         hiredate,
         sal,
         deptno,
         lag(hiredate,1,'2020-01-01') over(partition by deptno order by hiredate) as time1,
         lag(hiredate,2) over(partition by deptno order by hiredate) as time2

         lead(hiredate,1,'2020-01-01') over(partition by deptno order by hiredate) as time3,
         lead(hiredate,2) over(partition by deptno order by hiredate) as time4,

      from emp;

5、头尾函数：
    first_value
    last_value
  应用场景：截至到当前，按照日期排序查询第一个入职和最后一个入职员工的薪资
    first：分组后，每行即为当前组的第一个值
    last： 分组后，每行即为当前值，保持不变

     select
        ename,
        deptno,
        hiredate,
        sal,
        first_value(sal) over(partition by deptno order by hiredate) as first,
        last_value(sal) over(partition by deptno order by hiredate) as last
     from emp;

6、其他函数：
     nth_value()   截止到当前薪资，显示每个员工的薪资中排名第2或第3的薪资
     ntile()       将每个部门员工按照入职日期分成3组

  查询每个部门截至当前，薪资排在第二和第三的员工信息
     select
       ename,
       sal,
       deptno,
       nth_value(sal,2) over(partition by deptno order by hiredate) as second_score,
       nth_value(sal,3) over(partition by deptno order by hiredate) as third_score,

       ntile(3) over(partition by deptno order by hiredate) as nt
     from emp;


  取出每个部门的第一组员工：
     select
        *
        from(
            select
            ename,
            sal,
            hiredate,
            deptno,
            nth_value(sal,2) over(partition by deptno order by hiredate) as second_score,
            nth_value(sal,3) over(partition by deptno order by hiredate) as third_score,
            ntile(3) over(partition by deptno order by hiredate) as nt
     from emp) t where t.nt = 1;
"""